package com.atguigu.base;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * JDBC的操作
 */
public class JDBCOperation {

    /**
     * 测试查询单行单列
     *
     * @throws Exception
     */
    @Test
    public void testQuerySingleRowAndCol() throws Exception {
        // 1. 注册驱动(JDK6以后可以省略)

        // 2. 获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu?user=root&password=mysqlmima");

        // 3. 预编译SQL语句得到PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT COUNT(*) FROM t_emp");

        // 4. 执行SQL语句，接收返回的结果集
        ResultSet resultSet = preparedStatement.executeQuery();

        // 5. 遍历结果集, 下标是从1开始的
        while (resultSet.next()) {
            int count = resultSet.getInt(1);
            System.out.println(count); // 5
        }

        // 6. 释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }


    /**
     * 测试查询单行多列
     *
     * @throws Exception
     */
    @Test
    public void testQuerySingleRow() throws Exception {
        // 1. 注册驱动(JDK6以后可以省略)

        // 2. 获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu?user=root&password=mysqlmima");

        // 3. 预编译SQL语句得到PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT emp_id, emp_name, emp_salary, emp_age FROM t_emp WHERE emp_id = ?");

        // 4. 为占位符赋值, 然后执行SQL语句，接收返回的结果集
        preparedStatement.setInt(1, 5);
        ResultSet resultSet = preparedStatement.executeQuery();

        // 5. 遍历结果集
        String separator = "\t";
        while (resultSet.next()) {
            int emp_id = resultSet.getInt("emp_id");
            String emp_name = resultSet.getString("emp_name");
            double emp_salary = resultSet.getDouble("emp_salary");
            int emp_age = resultSet.getInt("emp_age");
            System.out.println(emp_id + separator + emp_name + separator + emp_salary + separator + emp_age);
        }

        // 6. 释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }


    /**
     * 测试查询多行多列
     *
     * @throws Exception
     */
    @Test
    public void testQueryMoreRow() throws Exception {
        // 1. 注册驱动(JDK6以后可以省略)

        // 2. 获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu?user=root&password=mysqlmima");

        // 3. 预编译SQL语句得到PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT emp_id, emp_name, emp_salary, emp_age FROM t_emp WHERE emp_age > ?");

        // 4. 为占位符赋值, 然后执行SQL语句，接收返回的结果集
        preparedStatement.setInt(1, 26);
        ResultSet resultSet = preparedStatement.executeQuery();

        // 5. 遍历结果集
        String separator = "\t";
        while (resultSet.next()) {
            int emp_id = resultSet.getInt("emp_id");
            String emp_name = resultSet.getString("emp_name");
            double emp_salary = resultSet.getDouble("emp_salary");
            int emp_age = resultSet.getInt("emp_age");
            System.out.println(emp_id + separator + emp_name + separator + emp_salary + separator + emp_age);
        }

        // 6. 释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }


    /**
     * 测试新增
     *
     * @throws Exception
     */
    @Test
    public void testInsert() throws Exception {
        // 1. 注册驱动(JDK6以后可以省略)

        // 2. 获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu?user=root&password=mysqlmima");

        // 3. 预编译SQL语句得到PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO t_emp (emp_name, emp_salary, emp_age) VALUES (?, ?, ?)");

        // 4. 为占位符赋值, 然后执行SQL语句，接收返回的结果
        preparedStatement.setString(1, "张三");
        preparedStatement.setDouble(2, 8500.00);
        preparedStatement.setInt(3, 38);
        int result = preparedStatement.executeUpdate();

        // 5. 根据受影响行数做判断, 得到成功和失败的结果
        System.out.println(result > 0 ? "插入" + result + "条数据成功" : "插入失败");

        // 6. 释放资源
        preparedStatement.close();
        connection.close();
    }


    /**
     * 测试删除
     *
     * @throws Exception
     */
    @Test
    public void testDelete() throws Exception {
        // 1. 注册驱动(JDK6以后可以省略)

        // 2. 获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu?user=root&password=mysqlmima");

        // 3. 预编译SQL语句得到PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM t_emp WHERE emp_id = ?;");

        // 4. 为占位符赋值, 然后执行SQL语句，接收返回的结果
        preparedStatement.setInt(1, 7);
        int result = preparedStatement.executeUpdate();

        // 5. 根据受影响行数做判断, 得到成功和失败的结果
        System.out.println(result > 0 ? "删除" + result + "条数据成功" : "删除失败");

        // 6. 释放资源
        preparedStatement.close();
        connection.close();
    }


    /**
     * 测试更新
     *
     * @throws Exception
     */
    @Test
    public void testUpdate() throws Exception {
        // 1. 注册驱动(JDK6以后可以省略)

        // 2. 获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu?user=root&password=mysqlmima");

        // 3. 预编译SQL语句得到PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE t_emp SET emp_name = ? WHERE emp_id = ?;");

        // 4. 为占位符赋值, 然后执行SQL语句，接收返回的结果
        preparedStatement.setString(1, "王五");
        preparedStatement.setInt(2, 7);
        int result = preparedStatement.executeUpdate();

        // 5. 根据受影响行数做判断, 得到成功和失败的结果
        System.out.println(result > 0 ? "修改" + result + "条数据成功" : "修改失败");

        // 6. 释放资源
        preparedStatement.close();
        connection.close();
    }
}
